﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CrmApplication.Models
{
	public class TStatusModels
	{
		#region RegionMappingStatus

		public decimal IdTStatus { get; set; }
		public string TStatusName { get; set; }

		public decimal IdCampaign { get; set; }
		public string IdCampaign_val { get; set; }
		public string CampaignName { get; set; }

		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

       /// <summary>
       /// database connection string
       /// </summary>
		static TStatusModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionStatus
		/// <summary>
		/// get all statuses/by campaign
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static List<TStatusModels> GetTStatus (decimal idCampaign)
		{
			string selbyString = "";
			if (idCampaign != 0)
			{
				selbyString = " where ts.id_campaign =:idCampaign ";
			}
			string selectString = " select ts.id_tstatus,ts.tstatus_name,c.campaign_name,ts.id_campaign "
									+ " from telestatus ts join campaign c on ts.id_campaign=c.id_campaign "
									+ selbyString;								

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idCampaign", idCampaign);

			List<TStatusModels> result = new List<TStatusModels>();
			TStatusModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TStatusModels();
						result1.IdTStatus = sdr.GetDecimal(0);
						result1.TStatusName = sdr.GetString(1);
						result1.CampaignName = sdr.GetString(2);
						result1.IdCampaign = sdr.GetDecimal(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// get selected status infos
		/// </summary>
		/// <param name="idTStatus"></param>
		/// <returns></returns>
		public static List<TStatusModels> GetTStatusById (decimal idTStatus)
		{
			string selectString = "select ts.id_tstatus,ts.tstatus_name,ts.id_campaign,c.campaign_name"
									+ " from telestatus ts join campaign c on ts.id_campaign=c.id_campaign "
									+ " where ts.id_tstatus=:id ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":id", idTStatus);

			List<TStatusModels> result = new List<TStatusModels>();
			TStatusModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new TStatusModels();
						result1.IdTStatus = sdr.GetDecimal(0);
						result1.TStatusName = sdr.GetString(1);
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.IdCampaign_val = sdr.GetString(3);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// modify status
		/// </summary>
		/// <param name="tstatus"></param>
		/// <returns></returns>
		public static TStatusModels EditTStatus (TStatusModels tstatus)
		{
			string selectString = "update telestatus set tstatus_name=:tstatusName,id_campaign=:idCampaign "
									+ " where id_tstatus=:idTStatus ";

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":tstatusName", tstatus.TStatusName);
			updateComm.Parameters.Add(":idCampaign", tstatus.IdCampaign);
			updateComm.Parameters.Add(":idTStatus", tstatus.IdTStatus);			

			TStatusModels result = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new TStatusModels();
						result.TStatusName = sdr.GetString(0);
						result.IdCampaign = sdr.GetDecimal(1);
						result.IdTStatus = sdr.GetDecimal(2);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new status
		/// </summary>
		/// <param name="tstatus"></param>
		public static void AddTStatus (TStatusModels tstatus)
		{
			string insert = "insert into telestatus(tstatus_name,id_campaign)" +
							"values (:tstatusName,:idCampaign)";

			OracleCommand usercomm = new OracleCommand(insert);
			usercomm.Parameters.Add("tstatusName", tstatus.TStatusName);
			usercomm.Parameters.Add("idCampaign", tstatus.IdCampaign);			

			usercomm.Connection = connect;

			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}
		#endregion

		#region RegionArrayJson
		/// <summary>
		/// create json array for all statuses
		/// </summary>
		/// <param name="idCampaign"></param>
		/// <returns></returns>
		public static TStatusContainer GetTStatusArray (decimal idCampaign)
		{
			TStatusModels[] data = new TStatusModels[20];
			data = TStatusModels.GetTStatus(idCampaign).ToArray();

			TStatusContainer container = new TStatusContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="idTStatus"></param>
		/// <returns></returns>
		public static TStatusContainer GetTStatusArrayById (decimal idTStatus)
		{
			TStatusModels[] data = new TStatusModels[20];
			data = TStatusModels.GetTStatusById(idTStatus).ToArray();

			TStatusContainer container = new TStatusContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion
	}

	public class TStatusContainer
	{
		public TStatusModels[] Records { get; set; }
		public int Count { get; set; }
	}
}